![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Tuning Joins for ThroughputTo specify that the join operation be optimized for maximum throughput, specify the USE_MERGE hint to force the optimizer to choose a sort-merge join. Most likely, this is the optimization approach the optimizer would choose by default anyway, unless another approach was defined with the Oracle initialization parameters. Tuning Joins for Response TimeTo specify that the join operation be optimized for response time, specify the USE_NL hint to force the optimizer to choose a nested-loops join. Remember that with the USE_NL hint, you must specify the inner table in the nested loop. On its own, the optimizer would not choose this approach.
LockingA book on Oracle performance is not complete without a discussion about locking. With Oracle version 6, the concept of row-level locking was introduced. Although row-level locking improves performance, there are conditions in which table-level locking is necessary. What Is Locking?In multiuser systems or servers, many users may update the same information at the same time. Locking allows this to occur and ensures that data you are currently modifying cannot be modified at the same time by a different user. The basic idea of locking is that when a user accesses data through a transaction, that data is locked (that is, it is exclusively owned by that transaction) until the transaction is committed or rolled back. Because data cannot be accessed by other transactions until the lock is released, it is to the benefit of the system that you reduce both the time the data is locked and the amount of data that is locked. Oracle provides two basic types of locks: the row-level lock and the table-level lock. When you use a row-level lock, only the row in the table that is being modified is locked. Other users can access data and update other rows in that table; they just cant update the particular row being modified. All rows in the table can be viewedeven the one being modified. Other users who view the row being modified see the old version of the data (through the rollback segment) until the changes are committed. Row-level locking is more efficient than table-level locking because it does not keep other transactions from running. OLTP systems benefit from row-level locking because of the large number of concurrent users who access the system. When you use a table-level lock, the entire table being modified is locked. Other users cant modify any rows in the table until the lock is released. As with the row-level lock, other users can still view data being modified; they see the old version of the data (through the rollback segments) until the changes are committed. Serializable ReadsIf your application requires serializable reads, you must change the parameters SERIALIZABLE and ROW_LOCKING in the Oracle initialization file. By changing these parameters, you alter the performance of the system dramatically. Oracle recommends that users implement applications using the default row locking. When you set the Oracle initialization parameter SERIALIZABLE to TRUE, queries acquire table-level read locks, thus preventing updates to the objects being read until the transaction containing the query has been committed. This mode provides for repeatable reads and ensures that two queries for the same data in the same transaction see the same data. This parameter provides for ANSI-degree-three consistencybut at a considerable cost in concurrency. I recommend that you do not set SERIALIZABLE unless it is absolutely necessary to correctly run older applications. In most cases, row-level locking is optimal.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |